﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AnOtherBlog.Core.Providers.SQL
{
	/// <summary>
	/// Implementa el acceso a datos para la clase Category
	/// </summary>
	public partial class SQLDataProvider : DataProvider
	{
		#region Implement Abstract Class
		/// <summary>
		/// Obtiene una categoría
		/// </summary>
		/// <param name="id">Identificador de la categoría</param>
		/// <returns>Categoria</returns>
		public override Category CategorySelect(Guid id)
		{
			Category category = null;
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"SELECT "
					+ @"CategoryID, CategoryName, Description, BlogID "
					+ @"FROM aob_Categories WHERE CategoryID=@CategoryID";

					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@CategoryID", id));

					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							category = SetCategoryProperties(reader);
						}
					}
				}
			}
			return category;
		}

		/// <summary>
		/// Inserta una categoría
		/// </summary>
		/// <param name="category">Categoría a insertar</param>
		public override void CategoryInsert(Category category)
		{
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"INSERT INTO aob_Categories "
					+ @"( CategoryID, CategoryName, Description, BlogID) VALUES "
					+ @"(@CategoryID,@CategoryName,@Description,@BlogID)";

					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@CategoryID", category.CategoryID));
					command.Parameters.Add(new SqlParameter("@CategoryName", category.CategoryName));
					command.Parameters.Add(new SqlParameter("@Description", category.Description));
					command.Parameters.Add(new SqlParameter("@BlogID", Blog.Current.BlogID));
					command.ExecuteNonQuery();
				}
			}
			category.IsNew = false;
		}

		/// <summary>
		/// Modifica una categoría
		/// </summary>
		/// <param name="category">Categoría a modificar</param>
		public override void CategoryUpdate(Category category)
		{
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"UPDATE aob_Categories SET"
					+ @" CategoryName = @CategoryName "
					+ @",Description = @Description "
					+ @" WHERE CategoryID=@CategoryID";

					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@CategoryID", category.CategoryID));
					command.Parameters.Add(new SqlParameter("@CategoryName", category.CategoryName));
					command.Parameters.Add(new SqlParameter("@Description", category.Description));
					command.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Suprime una categoría
		/// </summary>
		/// <param name="category">Categoría a suprimir</param>
		public override void CategoryDelete(Category category)
		{
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"DELETE FROM aob_Categories WHERE CategoryID=@CategoryID";
					command.CommandType = CommandType.Text;
					command.Parameters.Clear();
					command.Parameters.Add(new SqlParameter("@CategoryID", category.CategoryID));
					command.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Obtiene la lista de todas las categorías
		/// </summary>
		/// <returns>Colección de categorías</returns>
		public override List<Category> CategoryList()
		{
			List<Category> categories = new List<Category>();
			using (SqlConnection connection = DataConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;

					command.CommandText = @"SELECT "
					+ @"CategoryID, CategoryName, Description, BlogID "
					+ @"FROM aob_Categories WHERE BlogID=@BlogID "
					+ @"ORDER BY CategoryName ";

					command.CommandType = CommandType.Text;
					command.Parameters.Add(new SqlParameter("@BlogID", Blog.Current.BlogID));

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							categories.Add(SetCategoryProperties(reader));
						}
					}
				}
			}
			return categories;
		}

		#endregion


		#region Utilities Methods

		/// <summary>
		/// Fija todas las propiedades de una categoría
		/// </summary>
		/// <param name="reader">El <see cref="System.Data.SqlClient.SqlDataReader"/>que contiene los datos</param>
		/// <returns>Una nueva <see cref="AnOtherBlog.Core.Category"/> category</returns>
		private Category SetCategoryProperties(SqlDataReader reader)
		{
			Category category = new Category();

			if (!reader.IsDBNull(0)) category.CategoryID = reader.GetGuid(0);
			if (!reader.IsDBNull(1)) category.CategoryName = reader.GetString(1);
			if (!reader.IsDBNull(2)) category.Description = reader.GetString(2);
			if (!reader.IsDBNull(3)) category.BlogID = reader.GetInt32(3);

			category.IsNew = false;
			category.IsProxy = false;

			return category;
		}

		#endregion
	}
}
